#!/usr/bin/env python
# -*- coding: utf-8 -*-
""" This is the database library for tack.

You probably want the query function:

>>> from tack.db import query
>>> query
<function query at 0x...>
"""
import os
import re
from datetime import datetime
from string import Template
from time import strptime

from tack.lib.jsontools import json_dumps


def query(connection, statement, *parameters):
    """ Executes database query and returns its results.

    >>> import sqlite3
    >>> c = sqlite3.connect(':memory:')
    >>> query(c, "SELECT 1, 2, 3")
    [(1, 2, 3)]

    @param connection an open database connection
    @param statement sql statement to execute
    @param *parameters zero or more arguments for the statement
    @return database rows, if any
    """
    # If we're on Windows, we're (probably) using a driver that requires
    # a different substituion placeholder.
    # Yes, it's a hack.
    # Yes, this is a non-optimal solution, but it's what we'll be stuck with
    # until we want to write something higher level or more abstracted.
    if os.name == 'nt' or 'tack_lib_query_test' in os.environ:
        placeholder = '?'
    else:
        # No change, in other words.
        placeholder = None

    cursor = connection.cursor()

    if parameters:
        if placeholder:
            statement = statement.replace('%s', placeholder)
        results = cursor.execute(statement, parameters)
    else:
        results = cursor.execute(statement)

    if re.match("INSERT|UPDATE|DELETE.*", statement.strip(), re.I):
        connection.commit()
        rows = results if results is not cursor else []
    else:
        rows = cursor.fetchall()

    to_translate = []
    if cursor.description:
        for col_num, col in enumerate(cursor.description):
            if col[1] == datetime:
                to_translate.append(col_num)

    if len(to_translate) > 0:
        for row in rows:
            for col_num in to_translate:
                val = row[col_num]
                row[col_num] = val.strftime('%Y-%m-%d %H:%M:%S') if val else None
    return rows

action_rx = '^(?P<action>\w+)'
insert_rx = '^\w+\sinto\s(?P<relation>\w+)\s*\((?P<target>[\w,\s,.]+)\)\svalues\s*\((?P<values>[\S,\s]+)\)$'
update_rx = '^\w+\s(?P<relation>[\w,\s,.]+)\sset\s(?P<update_target>[\S,\s,.]+)\swhere\s(?P<conditional>[\S,\s]+)$'
delete_rx = '^\w+\sfrom\s(?P<relation>[\w,\s,.]+)\swhere\s(?P<conditional>[\S,\s]+)$'

changelog_sql = """
INSERT INTO changelog (changedate, loggedinuser, action, relation, datastring)
VALUES (%s, %s, %s, %s, %s)
"""

def logged_query(connection, statement, username='unknown', *parameters):
    """

    First a bit of prep work:

    >>> import sqlite3
    >>> import tack.db

    >>> con = sqlite3.connect(':memory:')
    >>> tack.db.query(con, 'CREATE TABLE changelog (changedate, loggedinuser, action, relation, datastring)')
    []

    >>> tack.db.query(con, 'CREATE TABLE authors (author_id, fname, lname)')
    []

    Jigger the default:

    >>> tack.db.changelog_sql = tack.db.changelog_sql.replace('%s', '?')
    >>> tack.db.logged_query(con, "INSERT INTO authors (author_id, fname, lname) VALUES (?, ?, ?)", '', 0, 'Mark', 'Twain')
    []

    >>> tack.db.query(con, "SELECT * FROM authors")
    [(0, u'Mark', u'Twain')]

    >>> tack.db.query(con, "SELECT * FROM changelog")
    [(u'...', u'', u'insert', u'authors', u'{"lname": "Twain", "author_id": 0, "fname": "Mark"}')]

    Insert done, now for the update
    >>> tack.db.logged_query(con, "UPDATE authors SET fname=?,lname=? WHERE author_id=0",'','Oscar','Wilde')
    []
    >>> tack.db.query(con, "SELECT * FROM authors")
    [(0, u'Oscar', u'Wilde')]

    >>> tack.db.query(con, "SELECT * FROM changelog")
    [(u'...', u'', u'insert', u'authors', u'{"lname": "Twain", "author_id": 0, "fname": "Mark"}'), (u'...', u'', u'update', u'authors', u'{"new": {"lname": "Wilde", "fname": "Oscar"}, "old": {"lname": "Twain", "fname": "Mark"}}')]


    Deleting the record, now.
    >>> tack.db.logged_query(con, "DELETE FROM authors WHERE author_id=0")
    []
    >>> tack.db.query(con, "SELECT * FROM authors")
    []
    >>> tack.db.query(con, "SELECT * FROM changelog")
    [(u'...', u'', u'insert', u'authors', u'{"lname": "Twain", "author_id": 0, "fname": "Mark"}'), (u'...', u'', u'update', u'authors', u'{"new": {"lname": "Wilde", "fname": "Oscar"}, "old": {"lname": "Twain", "fname": "Mark"}}'), (u'...', u'unknown', u'delete', u'authors', u'{"record": {"author_id": "0"}}')]
    """
    now = datetime.now()
    try:
        collection = re.search(action_rx, statement, re.I).groupdict()
    except (AttributeError, ):
        raise ValueError("Malformed statement.")
    action = collection['action'].lower()

    if action == 'insert':
        output = re.search(insert_rx, statement, re.I).groupdict()
        if parameters:
            targets = zip(re.split('\s*,\s*', output['target']),
                      parameters)
        else:
            targets = zip(re.split('\s*,\s*', output['target']),
                      re.split('\s*,\s*', output['values']))
        results = query(connection, statement, *parameters)
        query(connection, changelog_sql, now, username, action,
              output['relation'], json_dumps(dict(targets)))
        return results

    elif action == 'update':
        try:
            output = re.search(update_rx, statement, re.I).groupdict()
        except (AttributeError, ):
            raise ValueError("Malformed statement.")
        if parameters:
            rx = re.compile('\=%s|\=\?')
        else:
            rx = re.compile('\s*\=\s*\'\w*\'')
        fields = rx.sub('',output['update_target'])
        prior_sql = 'SELECT '+fields+' FROM '+output['relation']+' WHERE '+output['conditional']
        prior_data = query(connection, prior_sql)
        keys = re.split('\s*,\s*', fields)
        try:
            orig_data = zip(keys,prior_data[0])
        except (IndexError, ):
            raise ValueError("No matching data.")
        rx = re.compile('\w+\s*\=\s*')
        if parameters:
            new_data = zip(keys,
                           parameters)
        else:
            new_data = zip(keys, 
                          (re.split(',', (rx.sub('', output['update_target'])))))
        results = query(connection, statement, *parameters)
        query(connection, changelog_sql, now, username, action,
              output['relation'], json_dumps({'old':dict(orig_data),
                                              'new':dict(new_data)})
              )
        return results

    elif action == 'delete':
        try:
            output = re.search(delete_rx, statement, re.I).groupdict()
        except (AttributeError, ):
            raise ValueError("No condition statement.")
        rx = re.compile('\s*\=\s*\'*\s*\w+\s*\'*')  #strip off the ='data'
        fields = re.split(',', (rx.sub('', output['conditional'])))
        rx = re.compile('\s*\w+\s*\=\s*')           #strip off the field=
        data = zip(fields,
                  (re.split(',', (rx.sub('', output['conditional'])))))
        results = query(connection, statement, *parameters)
        query(connection, changelog_sql, now, username, action,
              output['relation'], json_dumps(dict(record=dict(data))))
        return results

    ## perhaps in the future, handle selects
    #elif action == 'select':
    #    pass

    else:
        raise ValueError('Malformed sql statement.')


def sql_int(v):
    try:
        return int(v)
    except (ValueError, ):
        return None


def sql_date(v, format):
    try:
        t = strptime(v, format)
    except (ValueError, ):
        return None
    else:
        return "'%s'" % v


def sql_float(v):
    try:
        return float(v)
    except (ValueError, ):
        return None


def sql_string(v):
    v = v.replace("'", "''")
    v = v.replace("\\", "\\\\")
    v = v.replace(";", "\;")
    v = "'%s'" % v
    return v


def sql_unstring(v):
    v = v.replace("\;", ";")
    v = v.replace("\\\\", "\\")
    v = v.replace("''", "'")
    return v


def template_sub(s, d):
    return Template(s).substitute(d)


def row_dict(row):
    desc = row.cursor_description
    return dict((c[0], row[i]) for i, c in enumerate(desc))


def row_dumps(o):
    return json_dumps(row_dict(o), indent=2)


def rows_dumps(o):
    return json_dumps([row_dict(r) for r in o], indent=2)
